  global restrdir "U:\data"
global public "R:\Public"
global rand "R:\Public\Contributions\Rand\RandHRS2016V2\stata"
global output "U:\output"

*-------------------------------------------------------------------------------
* Set real wage growth assumption 12 (1.2%) or 22 (2.2%)
* 1.2% is the intermediate 2020-2084 assumption in the SSA 2010 trustee report
* 2.2% is the intermediate 2010-2015 assumption in the SSA 2010 trustee report
*-------------------------------------------------------------------------------

global wg 22 //12 or 22

*-------------------------------------------------------------------------------
*Create tempfiles to store public data vars
*-------------------------------------------------------------------------------

use $public/XWave/PenWealthImp2010/built/stata/PWI10A_R, clear
gen double temp1 = real(HHID)
gen double temp2 = real(PN)
gen double respondent = temp1*1000 + temp2

keep respondent DCAcctBal DBFlag_10 HHID PN
tempfile pwi10a_r
save `pwi10a_r'

*RAND files for DC (own and employer contribution) in 2010
use hhid pn r10dccont* r10dcbene* using $rand/randhrs1992_2016v2, clear
gen double temp1 = real(hhid)
gen double temp2 = real(pn)
gen double respondent = temp1*1000 + temp2
egen respcontrib = rowtotal(r10dccont1 - r10dccont4)
egen empcontrib = rowtotal(r10dcbene1 - r10dcbene4)
keep respondent respcontrib empcontrib
tempfile dccontrib10
save `dccontrib10', replace

*-------------------------------------------------------------------------------
* 2010 DB wealth and earnings projection from PEP 
* worker-plan-projected year pseudo panel
*-------------------------------------------------------------------------------

*Pension wealth and earnings projections from PEP (private sector workers)
import delimited $restrdir\penval_2010_earnadj_$wg.csv, clear 
rename maxnrervd dbwealth

*Get PEP coders' flag for frozen/CB converted plans based off SPD's/F5500's  
tempfile codeids
preserve
	keep codeid
	duplicates drop 
	rename codeid CodingID
	merge 1:m CodingID using $restrdir/PlanInfo2010ReleaseV0, ///
	keepus(FrozenPlan)	
	keep if _merge==3
	duplicates drop	
	rename CodingID codeid
	save `codeids', replace
restore	

merge m:1 codeid using `codeids', keepus(FrozenPlan)
drop _merge

*Merge with unrestricted pension data 
merge m:1 respondent using `pwi10a_r' 
tab DBFlag_10 _merge 
//unmerged obs are either pure DC or public sector employees (i.e. excluded from pension calculator input)
//1 record does not have a codeid
keep if _merge==3
drop _merge

*Remove respondent-years in plans coded as (Frozen/CB converted) by HRS
drop if FrozenPlan==1

*Merge with DC contribution data (2010)
merge m:1 respondent using `dccontrib10'
keep if _merge==3
drop _merge

*Drop combo plans
drop if codeid>5000

*Get age in 2010, survey weight, and interview year
rename HHID hhid
rename PN pn
merge m:1 hhid pn using $public/Tracker/stata/trk2018tr_r, keepusing(mage mwgtr miwyear)
drop if _merge!=3
drop _merge
rename mage age_2010
rename mwgtr MWGTR
rename hhid HHID
rename miwyear MIWYEAR
rename pn PN

*DC contribution rates in 2010 wave
gen respcontrib_r = respcontrib/wage if age==age_2010
gen empcontrib_r =  empcontrib/wage if age==age_2010

*Winsorize contrib rates (>99) 
gen empcontrib_rW = empcontrib_r
qui sum empcontrib_r if DCAcctBal<., d
replace empcontrib_rW = r(p99) if empcontrib_rW>r(p99) & empcontrib_rW<. & DCAcctBal<. 
gen respcontrib_rW  = respcontrib_r
qui sum respcontrib_r if DCAcctBal<., d
replace respcontrib_rW = r(p99) if respcontrib_rW>r(p99) & respcontrib_rW<. & DCAcctBal<. 

*Sample of workers in private sector age 48+ with 2+ years of service
keep if age>=48 & asy>=2

*-------------------------------------------------------------------------------
* Collapse to worker-projected year level
*-------------------------------------------------------------------------------

*collapse to respondent-age level since some respondents have multiple plans
collapse (mean) spousebd bdate hiredate sex quitdate wage asy MWGTR  ///
age_2010 DCAcctBal respcontrib_rW empcontrib_rW ///
(sum) dbwealth, by(respondent age) 

*Contribution rates
bysort respondent: egen temp = max(respcontrib_rW)
replace respcontrib_rW = temp if respcontrib_rW==.
drop temp
bysort respondent: egen temp = max(empcontrib_rW)
replace empcontrib_rW = temp if empcontrib_rW==.
drop temp

*Real earnings in 2010 dollars (\pi = 0.028/year)
gen wage_2010 = wage*(1.028)^(2010-quitdate)

*Merge with annuity factors
merge m:1 age sex using $restrdir/annfactors
keep if _merge==3
drop _merge

*DC wealth by age based on 2010 balance
gen dcbal = DCAcctBal if age == age_2010
sort respondent age
gen contrib_tot = empcontrib_rW  + respcontrib_rW

*Total contribution (conditional on positive contribution)
sum contrib_tot if age == age_2010 & contrib_tot>0 
local contrib_av = r(mean)
*Worker contribution (conditional on positive contribution)
sum respcontrib_rW if age == age_2010 & respcontrib_rW>0 
local respcontrib_rW_av = r(mean)
*Impute total contrib using in-sample average for basic simulation
replace contrib_tot = `contrib_av' if contrib_tot==0 & DCAcctBal!=.

*Projected nominal DC wealth (backward and forward)
forvalues i = 1/20{
	replace dcbal = (1.028)*(1.029)*dcbal[_n-1] + wage*(contrib_tot) ///
	if respondent == respondent[_n-1] & age > age_2010 
}
forvalues i = 1/20{
	replace dcbal = max(((1.028*1.029)^-1*(dcbal[_n+1] - wage[_n+1]*(contrib_tot))),0) ///
	if respondent == respondent[_n+1] & age < age_2010 
}

*PV of DC balance in 2010 $ 
gen dcbal_2010 = dcbal*(1.028)^(2010-quitdate)
replace dcbal=0 if dcbal==.
replace dcbal_2010=0 if dcbal_2010==.

*Average contrib rates
matrix dc_rates=J(2,3,.)

*Worker (conditional average)
sum respcontrib_rW if age == age_2010 & respcontrib_rW>0 
matrix dc_rates[1,1]=r(N)
matrix dc_rates[1,2]=r(mean)
matrix dc_rates[1,3]=r(sd)/sqrt(r(N))
*Firm (conditional average)
sum empcontrib_rW if age == age_2010 & empcontrib_rW>0 
matrix dc_rates[2,1]=r(N)
matrix dc_rates[2,2]=r(mean)
matrix dc_rates[2,3]=r(sd)/sqrt(r(N))

*-------------------------------------------------------------------------------
*Compute value of pension and total compensation if plan is frozen 
*-------------------------------------------------------------------------------

xtset respondent age

*First calculate compensation in undisturbed counterfactual
gen dbaccrual_orig = d.dbwealth
gen dcaccrual_orig = d.dcbal_2010
gen total_comp = wage_2010*(1-respcontrib_rW) + dbaccrual_orig + dcaccrual_orig if dcbal_2010>0
replace total_comp = wage_2010 + dbaccrual_orig if dcbal_2010==0

*Convert real DB wealth to nominal DB wealth
gen dbwealth_nom = dbwealth*(1.028)^(quitdate-2010)

forvalues a = 49/80{
	qui{
	
		*Flag to balance the statistics over respondents observed in all periods after freeze age
		bysort respondent: egen minage = min(age)
		gen flag = (minage<=`a')
		
		*Nominal DB Wealth under freeze 
		gen temp = dbwealth_nom if age==`a'-1 & flag==1
		*Compute annuity equivalent of nominal DB wealth at time of freeze
		gen annfrz = temp/annfactor
		bysort respondent: egen temp2 = max(annfrz)
		replace annfrz = temp2 if annfrz==.
		drop temp temp2
		*Compute post-freeze nominal DB wealth
		gen nomdbwealth_frz = (annfrz*annfactor)
		replace nomdbwealth_frz = . if age<`a' & flag==1
		*Convert nominal post-freeze DB wealth to real post-freeze DB wealth
		gen dbwealth0_frz`a' = nomdbwealth_frz*(1.028)^(2010-quitdate)
		drop annfrz nomdbwealth_frz 
		
		*Flag for no DC balance at time of freeze
		bysort respondent: gen nodcbal_frz`a'= (dcbal==0 & age==`a')
		bysort respondent: egen temp = max(nodcbal_frz`a')
		replace nodcbal_frz`a' = temp
		drop temp
		*Those who have a DC plan with positive balance continue as is 
		gen dcbal_frz`a' = dcbal if flag==1
		*Those who do not have a DC plan at the time of the freeze start one @ average in-sample rate
		gen dc_contrib = (wage*`contrib_av') if nodcbal_frz`a'==1 & flag==1 & age>=`a'
		sort respondent age
		replace dcbal_frz`a' = dc_contrib if age==`a' & flag==1 & nodcbal_frz`a'==1
		replace dcbal_frz`a' = (1.028*1.029)*dcbal_frz`a'[_n-1] + dc_contrib if respondent == respondent[_n-1] ///
		& age > `a' & flag==1 & nodcbal_frz`a'==1
		*Convert nominal DC balance to 2010 $
		gen dcbal10_frz`a' = dcbal_frz`a'*(1.028)^(2010-quitdate)
			
		*Total pension wealth post-freeze
		gen tpwealth_frz`a' = dbwealth0_frz`a' + dcbal10_frz`a'	
		
		*Total compensation 
		gen rcontrib_helper = respcontrib_rW
		replace rcontrib_helper = `respcontrib_rW_av' if nodcbal_frz`a'==1 & flag==1 & age>=`a'
		gen dbaccrual = d.dbwealth0_frz`a' //real DB accrual after freeze
		replace dbaccrual = dbwealth0_frz`a' - dbwealth[_n-1] if age==`a' & respondent == respondent[_n-1]
		gen dcaccrual = d.dcbal10_frz`a' //real DC accrual
		gen total_comp_frz`a' = wage_2010*(1-rcontrib_helper) + dbaccrual + dcaccrual if age>=`a' //real total compensation
		replace total_comp_frz`a' = total_comp if age<`a'
		drop dc_contrib minage flag nodcbal_frz`a' dbaccrual dcaccrual rcontrib_helper
		

	}
}

*Drop some vars
drop dcbal dcbal_frz* 

*Total pension wealth in the undisturbed counterfactual
gen tpwealth = dbwealth + dcbal_2010

*Averages by age for export
drop contrib_tot annfactor dbwealth_nom
collapse (mean) dbwealth - tpwealth ///
(count) N=dbwealth [aweight=MWGTR], by(age) 
order N age dbwealth wage_2010 dcbal_2010 tpwealth total_comp
export delimited $output/compwealth_frz_w10_$wg.csv, replace datafmt
		










